﻿create PROCEDURE Sync.SP_Board_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Board_Temp TABLE 
		(
			BoardID UniqueIdentifier,
			[BoardNumber] int,
				[CommitteeID] uniqueidentifier,
				[BoardDate] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Board_Temp 
	   SELECT	Tbl.Col.value('BoardID[1]','uniqueidentifier') BoardID,
                Tbl.Col.value('BoardNumber[1]', 'int') [BoardNumber],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('BoardDate[1]', 'datetime') [BoardDate],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Board') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Board] ([BoardID],[BoardNumber],[CommitteeID],[BoardDate])
	SELECT t.[BoardID],t.[BoardNumber],t.[CommitteeID],t.[BoardDate]
	FROM  @Board_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Board_Tracking s
        WHERE t.BoardID = s.BoardID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Board]
	SET [Board].[BoardNumber] = t.[BoardNumber],[Board].[CommitteeID] = t.[CommitteeID],[Board].[BoardDate] = t.[BoardDate] 
	FROM [dbo].[Board] s JOIN @Board_Temp t 
    ON t.BoardID = s.BoardID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Board_Tracking r
				  WHERE t.BoardID = r.BoardID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Board]
    FROM @Board_Temp t JOIN [dbo].[Board] s
    ON t.BoardID = s.BoardID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














